"""
DBManger ver. 1.21
剥离条件转化功能，形成ConditionParserBase
DBManger ver. 1.20
添加返回类（数据集类）
添加字典支持

DBManger ver. 1.1
批量插入修复

DBManger ver. 1.0
使用列表进行数据库查询
"""
import os
import sqlite3
from os.path import dirname, join, exists, normcase
from typing import Union, Any, Iterable, TypeVar, Tuple, Callable, List, Optional, Dict


def execute_sql_script(sql: str, db_file=join(os.getcwd(), 'db.sqlite3')):
    """
    执行多条sql语句
    :param sql: sql语句
    :param db_file: 数据库文件
    :return: None or list or tuple
    """
    with sqlite3.connect(db_file) as conn:
        try:
            c = conn.cursor()
            c.executescript(sql)
            conn.commit()
        except Exception as e:
            print(e)
            print('dbfile: ', db_file)
            print('sql语句执行错误！')
            conn.rollback()


def do_db_init(sql_file=join(dirname(__file__), 'main.sql'),
               db_file=join(os.getcwd(), 'db.sqlite3')):
    sql_file = normcase(sql_file).replace('\\', '/')
    db_file = normcase(db_file).replace('\\', '/')

    if exists(db_file):
        print(f'数据库文件{db_file}存在，将对其更新！')
    else:
        print(f'数据库文件不存在，将创建新文件: {db_file}')

    if not exists(sql_file):
        print('sql文件不存在，请检查！')
    else:
        with open(normcase(sql_file), 'r', True, encoding='utf-8_sig') as f:
            sql = f.read()
            # print(sql)
            execute_sql_script(sql, db_file)

do_DB_init = do_db_init # 兼容性考虑


TDBResult = TypeVar('TDBResult', bound="DBResult")


class DBResult:
    def __init__(self, description: tuple = (), data: Union[list, tuple] = ()):
        self.description = description
        self.short_col = tuple([t[0] for t in self.description])
        self.data = data

    def row_count(self) -> int:
        return len(self.data)

    def col_count(self) -> int:
        return len(self.description)

    def order_by(self, col: Iterable, reverse: bool = False) -> TDBResult:
        if not col or not reverse:
            return self
        if type(col) is str:
            col = [col]
        _d = {}
        for d in self.as_dict():
            _d[tuple([d[c] for c in col])] = d
        tkeys = list(_d.keys())
        tkeys.sort(reverse=reverse)
        return DBResult(self.description, [_d[k] for k in tkeys])

    def distinct(self) -> TDBResult:
        return DBResult(self.description, list(set(self.data)))

    def as_dict(self) -> tuple:
        """
        输出列表型[{},{},{}...]
        """
        return tuple([dict(zip(self.short_col, v)) for v in self.data])

    def as_list(self) -> tuple:
        """
        :param
        :return: ((col_name,),[(data),(),...])
        """
        return self.short_col, self.data

    def __str__(self):
        return str(self.as_dict())

    def __repr__(self):
        return str(self.as_dict())

    def __len__(self):
        return self.row_count()

    def __iter__(self):
        self._cur = 0
        return self

    def __next__(self):
        if self._cur == self.row_count():
            raise StopIteration
        self._cur += 1
        return dict(zip(self.short_col, self.data[self._cur - 1]))

    def __getitem__(self, index: int):
        """
        index 为整型，为行号，返回行数据
        """
        if type(index) is int:
            if index < 0:
                raise IndexError('下标不能为负数')
            if index >= self.row_count():
                raise IndexError('下标所指示的行不存在')
            return dict(zip(self.short_col, self.data[index]))
        else:
            raise IndexError('下标必须为整型，但获取的下标为: {}!'.format(str(type(index))))

    def get_row(self, row: int) -> list:
        """
        返回某行数据
        :param row: 行号
        """
        return [self[row][k] for k in self.short_col]

    def get_col(self, col: Union[int, str]) -> Optional[List[Any]]:
        """
        返回某列数据
        :param col: 列号或列名
        """
        try:
            if type(col) is int:
                return [item[col] for item in self]
            else:
                _col = self.short_col.index(str(col))
                return [item[_col] for item in self]
        except IndexError as e:
            print('下标出错：', col, "类型", type(col))
            print('可选下标: ', list(range(len(self))))
            print('可选列名：', self.short_col)

    def filter(self, func: Callable[[Union[List, Tuple]], bool]) -> List:
        return list(filter(func, self.as_dict()))

    def map(self, func: Callable[[Union[List, Tuple]], Any]) -> List:
        return list(map(func, self.as_dict()))


class DBManager:
    """
    DB管理查询类，默认使用sqlite3，也可以在创建实例时，设置其他数据库的connect
    """
    db_file = join(os.getcwd(), 'db.sqlite3')
    conn = None
    cursor = None

    def __init__(self, conn=None):
        self.conn = conn

    def connect(self, db_file=None):
        """
        如果使用默认的sqlite3，则需使用该函数创建连接
        :return:
        """
        if db_file is not None:
            self.db_file = db_file
        if not exists(self.db_file):
            print('dbmanager: 数据库文件不存在！')
            return
        try:
            if self.conn is None:
                self.conn = sqlite3.connect(self.db_file)
                self.cursor = self.conn.cursor()
                print('dbmanager: 连接数据库成功！')
        except Exception as e:
            self.close()
            print(u'dbmanager: 连接数据库失败！信息：')
            print(e)

    def isConnect(self):
        return self.conn is not None

    def __del__(self):
        if self.isConnect():
            self.close()

    def close(self):
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None
        if self.conn is not None:
            self.conn.close()
            self.conn = None
            print('dbmanager: 关闭数据库连接！')
    @staticmethod
    def _condition_to_sql(condition: tuple):
        params = []
        if condition:
            _condition = []
            if isinstance(condition, dict):
                for k, v in condition.items():
                    if isinstance(v, (list, tuple)):
                        _placeholder = ','.join(['?'] * len(v))
                        _condition.append(f'{k} IN ({_placeholder})')
                        params.extend(v)
                    else:
                        _condition.append(f'{k} = ?')
                        params.append(v)
            elif isinstance(condition, (list, tuple)):
                for c in condition:
                    if isinstance(c, (list, tuple)):
                        if len(c) == 3:
                            c[1] = c[1].upper()
                            if c[1] == 'IN' or c[1] == 'NOT IN':
                                _placeholder = ','.join(['?'] * len(c[2]))
                                _condition.append(f'{c[0]} {c[1]} ({_placeholder})')
                                params.extend(c[2])
                            elif c[1] == 'BETWEEN' or c[1] == 'NOT BETWEEN':
                                _condition.append(f'{c[0]} {c[1]} ? AND ?')
                                params.extend(c[2])
                            else:
                                _condition.append(f'{c[0]} {c[1]} ?')
                                params.append(c[2])
                        elif len(c) == 2:
                            _condition.append(f'{c[0]} = ?')
                            params.append(c[1])
                        else:
                            print(f'condition {c} 不符合规范，忽略！')
                    else:
                        print(f'condition {c} 不符合规范，忽略！')

            where = 'WHERE ' + ' AND '.join(_condition) if _condition else ''
        else:
            where = ''

        return where, tuple(params)


    def select(self, table_name: str,
               cols: tuple = (),
               condition: Optional[Union[Tuple, List, Dict]] = (),
               order_by: Union[str, tuple] = (),
               distinct: bool = False) -> DBResult:
        """
        执行SQL的SELECT查询。

        :param order_by: 列名，可以包含'ASC'或'DESC'排序方向，例如：['col1', 'col2 ASC', 'col3 DESC']
        :param distinct: 是否需要distinct关键字
        :param table_name: 表名
        :param cols: 列名列表
        :param condition: 条件列表，每个条件为'(列名,运算符,值)'
        :return: DBResult实例，包含查询结果的描述和数据
        """
        if not self.conn:
            print('err: not connected')
            return DBResult()
        colstr = ', '.join(cols) if cols and isinstance(cols, (list, tuple)) else '*'
        where, params = self._condition_to_sql(condition)
        distinct_str = ' DISTINCT' if distinct else ''
        sqlstr = f"SELECT{distinct_str} {colstr} FROM {table_name} {where}"
        if not order_by:
            order_str = ''
        elif isinstance(order_by, str):
            order_str = ' ORDER BY ' + order_by
        elif isinstance(order_by, (list, tuple)):
            torder = []
            for o in order_by:
                if type(o) is str:
                    torder.append(o)
                elif type(o) is list and len(o) == 2:
                    torder.append(' '.join(o))
            order_str = ' ORDER BY ' + ', '.join(torder)
        else:
            order_str = ''
            print('order_by 不符合规范，忽略！')

        sqlstr = sqlstr + order_str
        print(f'DBManager select: {sqlstr}',  params)
        try:
            self.cursor.execute(sqlstr, params)
            return DBResult(self.cursor.description, self.cursor.fetchall())
        except sqlite3.OperationalError as e:
            print(f"Error executing SQL: {e}")
            return DBResult()

    def selectx(self, table_list: tuple,
                cols: tuple = (),
                condition: Optional[Union[Tuple, List, Dict]] = (),
                order_by: Union[str, tuple] = (),
                distinct: bool = False) -> TDBResult:
        """
        多表查询
        """
        def deal_join(col, val):
            if isinstance(val, str) and '.' in val:
                table_2, col_2 = val.split('.')
                if table_2 in table_list:
                    return f'JOIN {table_2} ON {col} = {val}'
                else:
                    return ''
            else:
                return ''


        _condition = []
        _join = [table_list[0]]
        if isinstance(condition, dict):
            condition = [(k, '=', v) for k, v in condition.items()]
        if isinstance(condition, (list, tuple)):
            for c in condition:
                if isinstance(c, (list, tuple)):
                    if len(c) == 3 and c[1] == '=' and '.' in c[2]:
                        _j = deal_join(c[0], c[2])
                        if  _j:
                            _join.append(_j)
                    elif len(c) == 2 and '.' in c[1]:
                        _j = deal_join(c[0], c[1])
                        if  _j:
                            _join.append(_j)
                    else:
                        _condition.append(c)
        if len(_join) == 1 and len(table_list) > 1:
            raise ValueError('conditon 必须有 join condition')
        tb = '\n'.join(_join)
        return self.select(tb, tuple([c + " as " + c.replace('.', '_') for c in cols]),
                           _condition, order_by, distinct)

    def insert(self, table: str, data_to_insert:Optional[dict] = None, **kwargs) -> bool:
        """
        插入数据到指定的表中。

        :param table: 表名
        :param data_to_insert: 待插入data
        :param kwargs: 字典，包含键值对，键为列名，值为要插入的数据
        :return: 插入是否成功
        """
        if not data_to_insert and not kwargs:
            print('data and kwargs can not be both empty')
            return False
        if data_to_insert:
            kwargs.update(data_to_insert)
        # 构建插入的列名和对应值的字符串
        columns = ', '.join(kwargs.keys())
        values = ', '.join('?' for _ in kwargs)
        # params = [v if isinstance(v, (str, bytes)) else self.cond.data_cover(v) for v in kwargs.values()]
        params = tuple(kwargs.values())
        sql = f"insert into {table} ({columns}) VALUES ({values})"

        try:
            print('DBManager insert data: ', kwargs, params)
            self.cursor.execute(sql, params)
            self.conn.commit()
            return True
        except sqlite3.OperationalError as e:
            print('insert err: ', e)
            print('sql= ', sql)
            self.conn.rollback()
            return False

    def insert_batch(self, table_name: str, cols: tuple, values: tuple):
        """
        批量插入
        :param table_name: 表名
        :param cols: 列名的元组 (列1, 列2, ...)
        :param values: 值的元组列表 [(值1,), (值2,), ...]
        :return: 成功则返回 True，否则返回 False
        """
        if isinstance(cols, list):
            cols = tuple(cols)
        if not len(cols) == len(values[0]):
            raise ValueError("每行的列数必须相同")
        colstr = '(' + ','.join(cols) + ')'
        t = ', '.join('?' for _ in cols)
        sql = f"INSERT INTO {table_name} {colstr} VALUES ({t})"
        values = [tuple(v) for v in values if len(v) == len(cols)]
        try:
            self.cursor.executemany(sql, values)
            self.conn.commit()
            print(f'DBManager batch insert data: ', {table_name: {'cols': cols, 'values': values}})
            return True
        except sqlite3.OperationalError as e:
            print('insert err: ', e)
            print('sql= ', sql)
            self.conn.rollback()
            return False

    def update(self, table_name, by: Union[Tuple, List, Dict], new: Optional[dict]=None, **kwargs):
        """
        更新表格数据

        :param table_name: 要更新的表名
        :param by: 列出的限制条件元组
        :param kwargs: 更新的键值对，键为列名，值为新的值
        :return: 若更新成功返回True，否则返回False
        """
        if not new and not kwargs:
            print('data and kwargs can not be both empty')
            return False
        if new:
            kwargs.update(new)
        if not by:
            print('update操作不允许条件为空！')
            return False
        where, params = self._condition_to_sql(by)
        if where == '':
            print('条件转化为空，update操作不允许条件为空！')
            return False

        kv_pairs = []
        params1 = []
        for k, v in kwargs.items():
            kv_pairs.append(f"{k}=?")
            params1.append(v)
        
        kv = ', '.join(kv_pairs)
        sql = f"update {table_name} set {kv} {where}"
        params1.extend(params)
        params = tuple(params1)
        print('DBManager update data: ', sql, params)
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            return True
        except sqlite3.OperationalError as e:
            print('update err: ', e)
            print('sql= ', sql)
            self.conn.rollback()
            return False
        except Exception as e:
            print('update unexpected error: ', e)
            self.conn.rollback()
            return False

    def delete(self, table_name, by: Union[Tuple, List, Dict]):
        """
        删除数据库表中的数据

        :param table_name: 要删除数据的表名
        :param by: 列出的限制条件元组，默认为空表示删除表中所有数据
        :return: 若删除成功返回True，否则返回False
        """
        # DELETE FROM "main"."User" WHERE ("id"=3)
        if not by:
            print('delete操作不允许条件为空！')
            return False
        where, params = self._condition_to_sql(by)
        if where == '':
            print('条件转化为空，delete操作不允许条件为空！')
            return False

        sql = f'DELETE FROM {table_name} {where}'
        print('DBManager delete data: ', sql, params)
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            return True
        except sqlite3.OperationalError as e:
            print('delete err: ', e)
            print('sql= ', sql)
            return False

    def count(self, table, condition: tuple = ()):
        t = self.select(table, ('count(*) as c',), condition)
        return t[0]['c'] if len(t) > 0 else 0


def get_all_table_name(db_file):
    print('查询数据库%s文件信息' % db_file)
    if not exists(db_file):
        print('文件%s不存在' % db_file)
        return tuple()
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute('SELECT name FROM sqlite_master WHERE type="table" ORDER BY name;')
    table_names = [row[0] for row in cur.fetchall()]
    conn.close()
    print('查询到表格：', table_names)
    return tuple(table_names)


def get_all_col_name(db_file, table_name):
    """
    查找sqlite3数据库中给定表格的列名
    """
    print('查询数据库%s文件信息' % db_file)
    if not exists(db_file):
        print('文件%s不存在' % db_file)
        return tuple()
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute('PRAGMA table_info(%s)' % table_name)
    col_list = [t[1].lower() for t in cur.fetchall()]
    conn.close()
    print('查询到表格%s列：', table_name, col_list)
    return tuple(col_list)


_sqlite3_type_python_map = {
    'INTEGER': 'int',
    'REAL': 'float',
    'TEXT': 'str',
    'BLOB': 'bytes',
    'BOOLEAN': 'bool',
    'DATETIME': 'str',
    'DATE': 'str',
    'TIME': 'str',
    'NUMERIC': 'float',    # 数值类型，可能是整数或浮点数
}
date_type_map = {
    'datetime': 'datetime',
    'date': 'date',
    'time': 'time',
}

specical_type_map = {
    'password': 'pwd',
    'pwd': 'pwd',
    'img': 'image',
    'imgs': 'images',
    'image': 'image',
    'images': 'images',
    'file': 'file',
    'files': 'files',
    'file_path': 'file',
}

def get_db_file_info(db_file, is_lower = True):
    """
    获取数据库文件信息
    """
    db_info = {}
    # print('查询数据库%s文件信息' % db_file)
    if not exists(db_file):
        print('文件%s不存在' % db_file)
        return db_info
    conn = sqlite3.connect(db_file)
    cur = conn.cursor()
    cur.execute('SELECT name FROM sqlite_master WHERE type="table" ORDER BY name;')
    table_names = [row[0] for row in cur.fetchall()]
    for table_name in table_names:
        cur.execute('PRAGMA table_info(%s)' % table_name)
        # 转化为dict {col_name: (col_type, col_nullable, col_default_value, col_pk)}
        # 其中col_pk为1表示为主键，0表示非主键
        # col_nullable为0表示非空，1表示可为空
        # col_default_value为默认值，若为NULL则为None
        if is_lower:
            table_name = table_name.lower()
        if table_name == 'sqlite_sequence':
            continue
        columns = []
        columns_detail = []
        # print('table_name: ', table_name)
        for row in cur.fetchall():
            # print(row)
            col_order = row[0]
            col_name = row[1].lower() if is_lower else row[1]
            _col_name = col_name.split('_')[0] if '_' in col_name else col_name
            col_type = row[2][:row[2].find('(')] if '(' in row[2] else row[2]
            col_type = col_type.lower() if is_lower else col_type
            # 修正类型
            if _col_name in specical_type_map:
                col_type = specical_type_map[_col_name]
            elif col_type in date_type_map:
                col_type = date_type_map[col_type]
            else:
                col_type = _sqlite3_type_python_map[col_type.upper()]
            col_nullable = True if row[3] else False
            col_default_value = row[4]
            col_pk = True if row[5] else False
            if col_default_value == 'NULL':
                col_default_value = None
            columns.append(col_name)
            columns_detail.append({
                'col_order': col_order,
                'col_name': col_name,
                'col_type': col_type,
                'col_nullable': col_nullable,
                'col_default_value': col_default_value,
                'col_pk': col_pk
            })
        db_info[table_name] = {
            'columns': tuple(columns),
            'columns_detail': tuple(columns_detail)
        }
        
    for t in conn.iterdump():
        # print(t)
        start = 'CREATE TABLE'
        if t.startswith(start):
            ts = t[len(start):t.find('(') - 1]
            if is_lower:
                table_name = ts.strip().replace('"', '').lower()
            else:
                table_name = ts.strip().replace('"', '')
            # print(table_name)
            db_info[table_name].update({'sql': t})
    conn.close()
    # print('查询到数据库信息：', db_info)
    return db_info
